/*
 * CustomerDAO.java 
 *
 * Created on February 19, 2007, 7:08 PM
 *
 * This class contains all the CRUD and seach methods for a customer. 
 */

package edu.byu.isys413.jjenkins.data;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author Jeff
 * 
 * This class contains all the CRUD and seach methods for a customer. 
 */
public class CustomerDAO {
    
    private static CustomerDAO instance = null;
    
    /** Creates a new instance of CustomerDAO */
    private CustomerDAO() {
    }
    
       
    
    /* This method implements the singleton pattern. It insures that only
     * on instance of CustomerDAO exists
     *
     * @return: CustomerDAO instance */
    
    public static synchronized CustomerDAO getInstance() {
        if (instance == null) {
            instance = new CustomerDAO();
        }
        return instance;
    }
    
    
    /* This method creates a new Customer BO with a unique QUID, puts the customer in
     * object in cache, and then returns it to the calling method.
     *
     * @return: CustomerBO: customer */
    
    public CustomerBO create() throws DataException {
        try {
            
            String id = GUID.generate();
            CustomerBO customer = new CustomerBO();
            customer.setId(id);
            Cache.getInstance().put(customer.getId(), customer);
            return customer;
        } catch (Exception e) {
            throw new DataException("Could not create GUID", e);
        }
        
    }
    
    
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     *
     * @Par: String: id
     * @Return: CustomerBO
     * @Throws: DataException
     */
    public synchronized CustomerBO read(String id) throws DataException {
        
        // Checks to see if the item is in cache
        Cache cache = Cache.getInstance();
        CustomerBO customer = (CustomerBO)cache.get(id);
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // If the item is not in cache a connection is checked out and
            // The item is read from the database.
            if(customer == null){
                try {
                    //reads in customer
                    customer = read(id, conn);
                    
                }catch (SQLException e) {
                    conn.rollback();
                    throw new DataException("Could not retrieve record for id=" + id, e);
                }
            }
            
            // After all items are read (customer, membership, interest) the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        }
        
        // Return Customer
        return customer;
    }
    
    /**
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  @Par: String: id, Connection conn
     *  @Return: CustomerBO
     *  @throws: DataException, SQLException
     */
    synchronized CustomerBO read(String id, Connection conn) throws SQLException, DataException {
        
        
        // Checks to see if the item is in cache
        Cache cache = Cache.getInstance();
        CustomerBO customer = (CustomerBO)cache.get(id);
        
        // Quieries that database
        if(customer == null){
            
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM customer WHERE customer_id = ?");
            pstmt.setString(1, id);
            pstmt.execute();
            ResultSet rs = pstmt.getResultSet();
            
            if (rs.next()) {
                customer = new CustomerBO();
                customer.setId(rs.getString("customer_id"));
                customer.setFirstName(rs.getString("first_name"));
                customer.setLastName(rs.getString("last_name"));
                customer.setPhone(rs.getString("phone"));
                customer.setEmail(rs.getString("email"));
                customer.setAddress(rs.getString("address"));
                customer.setCity(rs.getString("city"));
                customer.setState(rs.getString("state"));
                customer.setZip(rs.getString("zip"));
                
                // Gets membership if it exists and sets the customer and membership properties
                MemberBO mem = MembershipDAO.getInstance().readByCustomer(customer.getId(), conn);
                if(mem != null){
                    mem.setCustomer(customer);
                    customer.setMember(mem);
                }
                
                // Sets already in database
                customer.setObjectAlreadyInDB(true);
                
                // adds to cache
                cache.put(customer.getId(), customer);
                
            }else{
                throw new DataException("Object was not found in the database.");
            }
        }
        
        
        // returns customer
        return customer;
    }
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     *
     * @par CustomerBO customer
     * @throws DataExcpetion
     */
    public synchronized void save(CustomerBO customer) throws DataException {
        try {
            
            
            // retrieve a database connection from the pool
            ConnectionPool connPool = ConnectionPool.getInstance();
            
            Connection conn = connPool.get();
            
            
            try {
                
                // Saves Customer
                save(customer, conn);
                
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not save record for" + customer.getId(), e);
            }
            
            // After all items are saved (customer, membership, interest) the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            // touch the cache for the object
            Cache.getInstance().touch(customer.getId());
            
            
            
        } catch (DataException e) {
            throw new DataException("Could not save record for" + customer.getId(), e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not save record for" + customer.getId(), e);
        } catch (SQLException e) {
            throw new DataException("Could not save record for" + customer.getId(), e);
        }
        
        
    }
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the CustomerDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     *
     *  @par: CustomerBO, Connection conn
     *  @throws: SQLException, DataException
     */
    synchronized void save(CustomerBO customer, Connection conn) throws SQLException, DataException {
        
        // check the dirty flag in the object.  if it is dirty,
        // run update or insert
        if (customer.isDirty()) {
            if (customer.isObjectAlreadyInDB()) {
                update(customer, conn);
            }else{
                insert(customer, conn);
            }
            customer.setDirty(false);
        }
        
        // call save(bo, conn) on any subobjects (like CustomerDAO to MembershipDAO)
        MemberBO m = customer.getMember();
        if(m != null){
            MembershipDAO.getInstance().save(m, conn);
        }
        
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     *
     * @Par CustomerBO customer
     */
    private synchronized void update(CustomerBO customer, Connection conn) throws SQLException, DataException {
        
        PreparedStatement pstmt = conn.prepareStatement("UPDATE customer SET customer_id = ?, first_name = ?, last_name = ?, phone = ?, email = ?, address = ?, city = ?, state = ?, zip =? WHERE customer_id = ?");
        pstmt.setString(1, customer.getId());
        pstmt.setString(2, customer.getFirstName());
        pstmt.setString(3, customer.getLastName());
        pstmt.setString(4, customer.getPhone());
        pstmt.setString(5, customer.getEmail());
        pstmt.setString(6, customer.getAddress());
        pstmt.setString(7, customer.getCity());
        pstmt.setString(8, customer.getState());
        pstmt.setString(9, customer.getZip());
        
        pstmt.setString(10, customer.getId());
        pstmt.execute();
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     *
     * @Par CustomerBO customer
     */
    private synchronized void insert(CustomerBO customer, Connection conn) throws SQLException, DataException {
        
        // Creates prepared statemetn
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO customer VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, customer.getId());
        pstmt.setString(2, customer.getFirstName());
        pstmt.setString(3, customer.getLastName());
        pstmt.setString(4, customer.getPhone());
        pstmt.setString(5, customer.getEmail());
        pstmt.setString(6, customer.getAddress());
        pstmt.setString(7, customer.getCity());
        pstmt.setString(8, customer.getState());
        pstmt.setString(9, customer.getZip());
        
        //Executes prepated statement
        pstmt.execute();
        
        //Sets already in database true
        customer.setObjectAlreadyInDB(true);
        
    }
    
    
    /* We intentionally do not allow a customer object to be deleted because
     * it could mess up relationships in the database */
    
    public synchronized void delete(CustomerBO customer)throws DataException{
        /* We intentionally do not allow a customer object to be deleted because
         * it could mess up relationships in the database */
        throw new DataException("You can not delete a customer");
    }
    
    /*This method queries that database for a list of all customers. It gets the
     *customer_id, first_name, last_name, and phone number. It puts these values
     *in a string array and adds the array to a list.
     *
     *&return: list <String[]>
     */
    
    public List<String[]> getAll() throws DataException {
        
        // List to store the string array of customers
        List list = new LinkedList();
        
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // Creates statement and populates list
            try {
                
                PreparedStatement pstmt = conn.prepareStatement("SELECT customer_id, first_name, last_name, phone FROM customer");
                pstmt.execute();
                ResultSet rs = pstmt.getResultSet();
                
                while(rs.next()){
                    String[] line = new String[4];
                    line[0]= rs.getString("customer_id");
                    line[1]=rs.getString("first_name");
                    line[2]=rs.getString("last_name");
                    line[3]=rs.getString("phone");
                    list.add(line);
                    
                }
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not retrieve list of customers " + e);
            }
            
            // After all items are read (customer, membership, interest) the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve list of customers " + e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve list of customers " + e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve list of customers " + e);
        }
        
        // Return Customer
        return list;
        
    }
    
        /*This method queries that database for a list of all customers with a certain
         * first and last name. It adds these objects to a list and puts them
         * in cache
         *
         *&return: list <of customers>
         */
    public List<CustomerBO> getByName(String fname, String lname) throws DataException {
        // List to store the string array of customers
        List list = new LinkedList();
        
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // Creates statement and populates list
            try {
                
                // prepares statemnt
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM customer WHERE first_name = ? AND last_name = ?");
                pstmt.setString(1, fname);
                pstmt.setString(2, lname);
                pstmt.execute();
                ResultSet rs = pstmt.getResultSet();
                
                //get instance of cache to put objects in
                Cache cache = Cache.getInstance();
                
                while(rs.next()){
                    
                    CustomerBO customer = new CustomerBO();
                    customer.setId(rs.getString("customer_id"));
                    customer.setFirstName(rs.getString("first_name"));
                    customer.setLastName(rs.getString("last_name"));
                    customer.setPhone(rs.getString("phone"));
                    customer.setEmail(rs.getString("email"));
                    customer.setAddress(rs.getString("address"));
                    customer.setCity(rs.getString("city"));
                    customer.setState(rs.getString("state"));
                    customer.setZip(rs.getString("zip"));
                    list.add(customer);
                    cache.put(customer.getId(), customer);
                    
                }
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not retrieve list of customers " + e);
            }
            
            // After all items are read (customer, membership, interest) the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve list of customers " + e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve list of customers " + e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve list of customers " + e);
        }
        
        // Return Customer
        return list;
    }
        /* This method queries that database for a list of all customers with a certain
         * telephone number. It adds these objects to a list and puts them
         * in cache
         *
         *&return: list <of customers>
         */
    public List<CustomerBO> getByPhone(String phoneNumber) throws DataException {
        // List to store the string array of customers
        List list = new LinkedList();
        
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // Creates statement and populates list
            try {
                
                // prepares statemnt
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM customer WHERE phone = ?");
                pstmt.setString(1, phoneNumber);
                pstmt.execute();
                ResultSet rs = pstmt.getResultSet();
                
                //get instance of cache to put objects in
                Cache cache = Cache.getInstance();
                
                while(rs.next()){
                    
                    CustomerBO customer = new CustomerBO();
                    customer.setId(rs.getString("customer_id"));
                    customer.setFirstName(rs.getString("first_name"));
                    customer.setLastName(rs.getString("last_name"));
                    customer.setPhone(rs.getString("phone"));
                    customer.setEmail(rs.getString("email"));
                    customer.setAddress(rs.getString("address"));
                    customer.setCity(rs.getString("city"));
                    customer.setState(rs.getString("state"));
                    customer.setZip(rs.getString("zip"));
                    list.add(customer);
                    cache.put(customer.getId(), customer);
                    
                }
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not retrieve list of customers " + e);
            }
            
            // After all items are read (customer, membership, interest) the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve list of customers " + e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve list of customers " + e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve list of customers " + e);
        }
        
        // Return Customer
        return list;
        
    }
    
    
    
    
}

